﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using PMPUtils.DB;
using PMPUtils.Resources;

namespace PMPUtils.Goals
{
    public class GoalDetailsOperator
    {

        public static void InitializeGoalDetailsGridView(GridView grvGoalDetails)
        {
            grvGoalDetails.Columns.Clear();

            if (grvGoalDetails.Columns.Count == 0)
            {
                // Add column
                ButtonField pickColumn = new ButtonField();
                pickColumn.ButtonType = ButtonType.Link;
                pickColumn.CommandName = "ManageGoalDetails";
                pickColumn.Text = "Manage";

                BoundField Task = new BoundField();
                Task.HeaderText = "Task";
                Task.DataField = "task_id";

                BoundField owner = new BoundField();
                owner.HeaderText = "Owner";
                owner.DataField = "owner";

                BoundField status = new BoundField();
                status.HeaderText = "Status";
                status.DataField = "status";

                BoundField due_date = new BoundField();
                due_date.HeaderText = "Due Date";
                due_date.DataField = "due_date";

                BoundField est_due_date = new BoundField();
                est_due_date.HeaderText = "Expected Due Date";
                est_due_date.DataField = "est_due_date";

                BoundField orig_est = new BoundField();
                orig_est.HeaderText = "Orig";
                orig_est.DataField = "orig_est";
                orig_est.DataFormatString = "{0:F0}";

                BoundField rem_est = new BoundField();
                rem_est.HeaderText = "Rem";
                rem_est.DataField = "rem_est";
                rem_est.DataFormatString = "{0:F0}";

                BoundField actual = new BoundField();
                actual.HeaderText = "Actual";
                actual.DataField = "actual";
                actual.DataFormatString = "{0:F0}";

                BoundField ETC = new BoundField();
                ETC.HeaderText = "ETC";
                ETC.DataField = "ETC";
                ETC.DataFormatString = "{0:F0}";

                BoundField EAC = new BoundField();
                EAC.HeaderText = "EAC";
                EAC.DataField = "EAC";
                EAC.DataFormatString = "{0:F0}";

                BoundField variance = new BoundField();
                variance.HeaderText = "Variance";
                variance.DataField = "variance";
                variance.DataFormatString = "{0:P0}";

                BoundField idColumn = new BoundField();
                idColumn.HeaderText = "ID";
                idColumn.DataField = "id";

                BoundField parentColumn = new BoundField();
                parentColumn.HeaderText = "Parent";
                parentColumn.DataField = "parent_id";

                grvGoalDetails.Columns.Add(pickColumn);
                grvGoalDetails.Columns.Add(Task);
                grvGoalDetails.Columns.Add(owner);
                grvGoalDetails.Columns.Add(status);
                grvGoalDetails.Columns.Add(due_date);
                grvGoalDetails.Columns.Add(est_due_date);
                grvGoalDetails.Columns.Add(orig_est);
                grvGoalDetails.Columns.Add(actual);
                grvGoalDetails.Columns.Add(rem_est);
                grvGoalDetails.Columns.Add(ETC);
                grvGoalDetails.Columns.Add(EAC);
                grvGoalDetails.Columns.Add(variance);
                grvGoalDetails.Columns.Add(idColumn);
                grvGoalDetails.Columns.Add(parentColumn);

                // List Data
                String strQuery = "select task_id, task_desc, usr.usr_name as owner, status, due_date, est_due_date, orig_est, actual,  rem_est, ETC, EAC, variance, goal_dtl.id, parent_id " +
                     " from goal_dtl " +
                     " join usr on (usr.id = goal_dtl.owner) " +
                     " where 1 = 2";
                DBOperator localDBOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
                DataTable dtGoalDetailss = localDBOperator.Read(strQuery);

                grvGoalDetails.DataSource = dtGoalDetailss.DefaultView;
                grvGoalDetails.DataBind();
            }
        }

        /// <summary>
        /// Refresh the goal details gridview
        /// </summary>
        /// <param name="grvGoalDetails"></param>
        public static void RefreshGoalDetailsGridView(GridView grvGoalDetails, String goal)
        {
            DataTable dtCurrentdetails = ReadGoalDetails(goal);

            grvGoalDetails.DataSource = dtCurrentdetails.DefaultView;
            grvGoalDetails.DataBind();
        }

        /// <summary>
        /// Read all details for the specific goal.
        /// </summary>
        /// <param name="goal"></param>
        /// <returns></returns>
        public static DataTable ReadGoalDetails(String goal)
        {
            String strQuery = "select task_id, task_desc, usr.usr_name as owner, status, due_date, est_due_date, orig_est, actual,  rem_est, ETC, EAC, variance, goal_dtl.id, parent_id " +
                     " from goal_dtl " +
                     " left outer join usr on (usr.id = goal_dtl.owner) " +
                     " where rev is null and parent_id = " + goal +
                     "  order by status, task_id";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCurrentdetails = dbOperator.Read(strQuery);

            return dtCurrentdetails;
        }

        /// <summary>
        /// Read the goal details with completion status.
        /// </summary>
        /// <param name="goal"></param>
        /// <returns></returns>
        public static DataTable ReadCompletedGoalDetails(String goal)
        {
            String strQuery = "select task_id, task_desc, usr.usr_name as owner, status, due_date, est_due_date, orig_est, actual,  rem_est, ETC, EAC, variance, goal_dtl.id, parent_id " +
                     " from goal_dtl " +
                     " join usr on (usr.id = goal_dtl.owner) " +
                     " where rev is null and parent_id = " + goal + " and status = 'Complete'" +
                     "  order by task_id";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCompletedDetails = dbOperator.Read(strQuery);

            return dtCompletedDetails;
        }

        /// <summary>
        /// Read the details not completed yet.
        /// </summary>
        /// <param name="goal"></param>
        /// <returns></returns>
        public static DataTable ReadInProgressGoalDetails(String goal)
        {
            String strQuery = "select task_id, task_desc, usr.usr_name as owner, status, due_date, est_due_date, orig_est, actual, rem_est, ETC, EAC, variance, goal_dtl.id, parent_id " +
                     " from goal_dtl " +
                     " join usr on (usr.id = goal_dtl.owner) " +
                     " where rev is null and parent_id = " + goal + " and status <> 'Complete'" +
                     "  order by task_id";

            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtCompletedDetails = dbOperator.Read(strQuery);

            return dtCompletedDetails;
        }

        /// <summary>
        /// Read the tips from the local database
        /// </summary>
        /// <param name="goalDetailID"></param>
        /// <returns></returns>
        public static String ReadTips(String goalDetailID)
        {
            String tip = String.Empty;

            String strQuery = "select task_desc, status from goal_dtl where id = " + goalDetailID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            DataTable dtSummary = dbOperator.Read(strQuery);

            foreach (DataRow row in dtSummary.Rows)
            {
                if (row["task_desc"] != DBNull.Value)
                {
                    String status = row["status"].ToString();
                    String summary = row["task_desc"].ToString();

                    tip = "( " + status + " ) " + summary;
                    break;
                }
            }

            return tip;
        }

        /// <summary>
        /// Verify whether the goal detail exists
        /// </summary>
        /// <param name="goal"></param>
        /// <returns></returns>
        public static bool IsGoalDetailExists(String goal, String taskID)
        {
            String strQuery = "select COUNT(task_id) exist from goal_dtl where parent_id = " + goal + " and task_id = '" + taskID + "' and rev is null";
            DataTable dtGoalDetailExist = new DataTable();
            bool exists = false;

            DBOperator dbOpeartor = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dtGoalDetailExist = dbOpeartor.Read(strQuery);

            foreach (DataRow row in dtGoalDetailExist.Rows)
            {
                if (row["exist"] != DBNull.Value)
                {
                    if (Convert.ToInt32(row["exist"]) >= 1)
                    {
                        exists = true;
                    }
                }
            }

            return exists;
        }

        /// <summary>
        /// Add a new detail to the goal.
        /// </summary>
        /// <param name="goal"></param>
        /// <param name="taskID"></param>
        /// <param name="owner"></param>
        /// <param name="status"></param>
        /// <param name="dueDate"></param>
        public static void AddNewGoalDetail(String goal, String taskID, String owner, String dueDate)
        {
            String strInsert = "insert into goal_dtl (parent_id, task_id, owner, due_date) values ";

            if (!owner.Equals(String.Empty))
            {
                strInsert += " ('" + goal + "', '" + taskID + "', " + owner + ", '" + dueDate + "')";
            }
            else
            {
                strInsert = "insert into goal_dtl (parent_id, task_id, due_date) values " +
                    " ('" + goal + "', '" + taskID + "', '" + dueDate + "')";
            }

            DBOperator localDBOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            localDBOperator.Update(strInsert);
        }

        /// <summary>
        /// Remove all the details of the goal.
        /// </summary>
        /// <param name="goalID"></param>
        public static void RemoveAllDetails(String goalID)
        {
            String strDelete = "delete from goal_dtl where parent_id = " + goalID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strDelete);
        }

        /// <summary>
        /// Remove the specified goal detail.
        /// </summary>
        /// <param name="goalDetailID">The ID of the detail.</param>
        public static void RemoveGoalDetail(String goalDetailID)
        {
            String strDelete = "delete from goal_dtl where id = " + goalDetailID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strDelete);
        }

        /// <summary>
        /// Switch the owner of the goal detail
        /// </summary>
        /// <param name="goalDetailID">ID of the goal detail</param>
        /// <param name="newOwner">User ID of the new owner</param>
        public static void SwitchOwner(String goalDetailID, String newOwner)
        {
            String strUpdate = "update goal_dtl set owner = " + newOwner + " where id = " + goalDetailID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the status of the goal detail
        /// </summary>
        /// <param name="goalDetailID">ID of the goal detail.</param>
        /// <param name="newStatus">ID of the status.</param>
        public static void ChangeStatus(String goalDetailID, String newStatus)
        {
            String strUpdate = "update goal_dtl set status = " + newStatus + " where id = " + goalDetailID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the due date of the goal detail.
        /// </summary>
        /// <param name="goalDetailID">ID of the goal detail.</param>
        /// <param name="newDueDate">New due date - 2011-08-11</param>
        public static void ChangeDueDate(String goalDetailID, String newDueDate)
        {
            String strUpdate = "update goal_dtl set due_date = '" + newDueDate + "' where id = " + goalDetailID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }

        /// <summary>
        /// Update the expected due date of the goal detail.
        /// </summary>
        /// <param name="goalDetailID">ID of the goal detail.</param>
        /// <param name="newExpectedDueDate">New due date - 2011-08-11</param>
        public static void ChangeExpectedDueDate(String goalDetailID, String newExpectedDueDate)
        {
            String strUpdate = "update goal_dtl set est_due_date = '" + newExpectedDueDate + "' where id = " + goalDetailID;
            DBOperator dbOperator = new DBOperator(DBConfigurations.LOCAL_CONNECTION_STRING);
            dbOperator.Update(strUpdate);
        }
    }
}
